package utils;

import jade.util.leap.ArrayList;
import jade.util.leap.List;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Random;

import ontology.concepts.employmentlaw.Norm;

public class CaseGenerator 
{
	private static final long serialVersionUID = 464937464163563007L;
	private static Connection C;
	private static Statement Stmt;
	
	public static void main(String [ ] args)
	{
		C = connectDB();
		System.out.println(" : Connected to Database");
		
		//TODO: ADD INFORMATION TO THE OUTCOME TABLE
		addCases(1);
		printStats();
	}
	
	private static void addCases(int amount)
	{
		for (int j = 0; j<amount; j++)
		{
			//Generate random CaseID
			String caseID = ""+System.currentTimeMillis();
			
			Random r = new Random();
			//Generate random partiesID
			String party1 = Long.toString(Math.abs(r.nextLong()), 36);  //EMPLOYEE
			String party2 = Long.toString(Math.abs(r.nextLong()), 36);  //EMPLOYER
			try 
			{
				Stmt = C.createStatement();
				Stmt.executeUpdate("INSERT into UMCourtL.roles(id, partyid, role) VALUES ('"+party1+"','party1')");
				Stmt.executeUpdate("INSERT into UMCourtL.roles(id, partyid, role) VALUES ('"+party2+"','party2')");
				
				//Generate random number of laws for party1
				int nparty1 = 0;
				int nparty2 = 0;
				while (nparty1 == 0)
					nparty1 = r.nextInt(4);
				while (nparty2 == 0)
					nparty2 = r.nextInt(4);
				List norms1 = new ArrayList();
				List norms2 = new ArrayList();
				
				//Generate laws for each party
				//Select all norms in database
				ResultSet result = null;
			
				Statement select = C.createStatement();
			    result = select.executeQuery("SELECT article, item, number FROM umCourtl.norms");
			    
			    int size = 0;
			    while (result.next())
			    	size ++;
			    
				//select n norms from norms list for both parties
				for (int i=0;i < nparty1; i++)
				{
					int n = r.nextInt(size) + 1;
					
					result.absolute(n);
			        int article = result.getInt(1);
			        String item = result.getString(2);
			        int number  = result.getInt(3);
		
			        Norm norm = new Norm();
			        norm.setArticle(""+article);
			        norm.setItem(item);
			        norm.setNumber(""+number);
			        norm.setDescription("");
					norms1.add(norm);
				}
				for (int i=0;i < nparty2; i++)
				{
					int n = r.nextInt(size) + 1;
					
					result.absolute(n);
			        int article = result.getInt(1);
			        String item = result.getString(2);
			        int number  = result.getInt(3);
		
			        Norm norm = new Norm();
			        norm.setArticle(""+article);
			        norm.setItem(item);
			        norm.setNumber(""+number);
			        norm.setDescription("");
					norms2.add(norm);
				}
				
				int bit =  0;
				if (Math.random() < 0.4)
					bit = 1;
				
				//Add the case to do database
				Stmt = C.createStatement();
				Stmt.executeUpdate("INSERT into umcourtl.casebase(caseid, party1id, party2id, party1wins) VALUES ('"+caseID+"','"+party1+"','"+party2+"','"+bit+"')");
				
				for (int i=0; i<norms1.size();i++)
				{
					Norm norm = (Norm) norms1.get(i);
					Stmt = C.createStatement();
					Stmt.executeUpdate("INSERT into umcourtl.normscase(caseid, partyid, article, number, item) VALUES ('"+caseID+"','"+party1+"','"+norm.getArticle()+"','"+norm.getNumber()+"','"+norm.getItem()+"')");
				}
				for (int i=0; i<norms2.size();i++)
				{
					Norm norm = (Norm) norms2.get(i);
					Stmt = C.createStatement();
					Stmt.executeUpdate("INSERT into umcourtl.normscase(caseid, partyid, article, number, item) VALUES ('"+caseID+"','"+party2+"','"+norm.getArticle()+"','"+norm.getNumber()+"','"+norm.getItem()+"')");
				}
				//add outcome
				int daysReceived = 15 + (int)(Math.random() * (45 - 15));
				int nightWork = (int)(Math.random() * 100);
				int supplementaryWork = (int)(Math.random() * 100);
				Stmt = C.createStatement();
				Stmt.executeUpdate("INSERT into UMCourtL.outcomes(idoutcomes, daysReceived, nightWork, suplementaryWork) VALUES ('"+caseID+"','"+daysReceived+"','"+nightWork+"','"+supplementaryWork+"')");
			}
			catch(com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException e){System.out.println("Trying to add a norm that is already addressed by party...");}
			catch (Exception e) {e.printStackTrace();}
		}
		System.out.println(amount+" cases added to the database.");
	}
	
	private static void printStats()
	{
		try
		{
			System.out.println("Statistics of the case base:\n	");
			//number of cases
			Statement select = C.createStatement();
		    ResultSet result = select.executeQuery("SELECT caseid FROM umCourtl.casebase");
		    
		    int count = 0;
		    while (result.next())
		    	count++;
		    
		    //Count different articles
		    int articlescount = 0;
		    select = C.createStatement();
		    result = select.executeQuery("SELECT article FROM umCourtl.normscase");
		    HashMap articles = new HashMap();
		    
		    while (result.next())
		    {
		    	articlescount++;
		    	int article = result.getInt(1);
		    	if (articles.containsKey(article))
		    	{
		    		int temp = ((Integer) articles.get(article)) + 1;
		    		articles.remove(article);
		    		articles.put(article, temp);
		    	}
		    	else articles.put(article, 1);
		    }
		    
		    Iterator it = articles.keySet().iterator();
		    while(it.hasNext())
		    {
		    	int key = (Integer) it.next();
		    	int value = (Integer) articles.get(key);
		    	
		    	//select description of article
		    	select = C.createStatement();
			    result = select.executeQuery("SELECT description FROM umCourtl.norms WHERE item IS NULL AND number IS NULL AND article='"+key+"'");
		    	result.next();
		    	
			    String description = result.getString(1);
		    	System.out.println("Article "+key+", "+description+", "+(value*100/articlescount)+"%");
		    }
		    
		    System.out.println("\n\nTotal cases in the database: "+count);
		    
		}
		catch(Exception e){e.printStackTrace();}
		
	}
	private static Connection connectDB()
	{
		//Connect to Database
		try {Class.forName("com.mysql.jdbc.Driver").newInstance();}
	    catch (Exception E) 
	    {
	    	System.err.println("Unable to load driver");
	        E.printStackTrace();
	    }

	    try 
	    {
	    	C = DriverManager.getConnection("jdbc:mysql://tiaracserver.di.uminho.pt:3306","dave","d3LLdav#");
	    }
	    catch (SQLException E) 
	    {
	        System.out.println("SQLException: " + E.getMessage());
	        System.out.println("SQLState:     " + E.getSQLState());
	        System.out.println("VendorError:  " + E.getErrorCode());
	    }
	    
	    return C;
	}

}
